package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import entity.JavaBean;

public class StudentDao {
	
	private final static String driverName = "com.mysql.cj.jdbc.Driver";//驱动名，旧版为com.mysql.jdbc.Driver
	private final static String url = "jdbc:mysql://localhost:3306/dbjsp?"
			+ "user=root&password=root&useUnicode=true&characterEncoding=UTF-8"
			+ "&serverTimezone=UTC&useSSL=false";//数据库链接url
	
	
	//查找
	public static boolean checkLogin(String username,String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		boolean ifLogin=false;
	
		
		try {
			
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from user where username=? and password=?");
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();	
			if(rs.next()) ifLogin=true;
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return ifLogin;
	
	}
	
	
	
	//读取其中一组
	public static List<JavaBean> readOneList(int i) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		List<JavaBean> list=new ArrayList<JavaBean>();
		
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url);
			ps = conn.prepareStatement("select * from person where id=?");
			ps.setInt(1, i);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String sex=rs.getString("sex");
				String pId=rs.getString("pId");
				java.sql.Date birthdate=utilToSql(rs.getDate("birthdate"));
				JavaBean dg=new JavaBean(id, pId, name, sex, birthdate);
				list.add(dg);		
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	//读取
	public static List<JavaBean> readList() {
		Connection conn = null;//数据库连接
		PreparedStatement ps = null;//sql预编译语句
		ResultSet rs=null;//执行后返回结果集
		List<JavaBean> list=new ArrayList<JavaBean>();//返回查询结果为list
		
		try {
			Class.forName(driverName);//加载驱动
			conn = DriverManager.getConnection(url);//与数据库建立连接
			ps = conn.prepareStatement("select * from person");//创建insert的sql预编译语句
			rs = ps.executeQuery();//执行sql语句，并将结果返回给结果集rs
			
			//此循环的意义在于，将查询返回的结果集rs里的一条一条数据，都add进集合list
			while(rs.next()) {
				int id=rs.getInt("id");
				String name=rs.getString("name");
				String sex=rs.getString("sex");
				String pId=rs.getString("pId");
				java.sql.Date birthdate=utilToSql(rs.getDate("birthdate"));
				JavaBean dg=new JavaBean(id, pId, name, sex, birthdate);
				list.add(dg);
				
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			try {
			if(rs!=null) {
				rs.close();
			}
			if(ps!=null) {
				ps.close();
			}
			if(conn!=null) {
				conn.close();
				}
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;//以集合形式返回查询结果
	}

	 	
	
	public static java.sql.Date utilToSql(Date date) {
		return new java.sql.Date(date.getTime());
	}
	
	// 添加
	@SuppressWarnings("static-access")
	public static void insert(int id, String pld, String name, String sex, java.util.Date birthdate) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			Class.forName(driverName);//加载驱动
			conn = DriverManager.getConnection(url);//与数据库建立连接
			ps = conn.prepareStatement("insert into person values(?,?,?,?,?)");//创建insert的sql预编译语句
			
			//set语句为sql中的“?”赋值，“1”代表第一个疑问号，以此类推
			ps.setInt(1, id);
			ps.setString(2, pld);
			ps.setString(3, name);
			ps.setString(4, sex);
			
			Calendar c = new GregorianCalendar(); 
			c.setTime(birthdate);
			c.add(c.DATE,1);
			birthdate=c.getTime();
			
			java.sql.Date date2 = new java.sql.Date(birthdate.getTime());
			ps.setDate(5, date2);

			//执行sql语句
			ps.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();//抛出异常
		} finally {
			//以下为释放资源
			try {
				if (ps != null) {
					ps.close();//关闭预编译语句
				}
				if (conn != null) {
					conn.close();//关闭数据库连接
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
	
	
	//删除
	public static void delete(int id){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			Class.forName(driverName);//加载驱动
			conn = DriverManager.getConnection(url);//与数据库建立连接
			ps = conn.prepareStatement("delete from person where id=?");//创建delete的sql预编译语句
			ps.setInt(1, id);//set语句为sql中的“?”赋值，“1”代表第一个疑问号，以此类推
			ps.executeUpdate();//执行sql语句
		} catch (Exception e) {
			e.printStackTrace();//抛出异常
		} finally {
			//以下为释放资源
			try {
				if (ps != null) {
					ps.close();//关闭预编译语句
				}
				if (conn != null) {
					conn.close();//关闭数据库连接
				}
			} catch (Exception e2) {
				e2.printStackTrace();//抛出异常
			}
		}
	}
	
	public ResultSet  Search(String sql, String str[]) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
        try {
        	Class.forName(driverName);
			conn = DriverManager.getConnection(url);
            ps =conn.prepareStatement(sql);
            if (str != null) {
                for (int i = 0; i < str.length; i++) {
                    ps.setString(i + 1, str[i]);
                }
            }
            rs = ps.executeQuery();

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
	
	public int AddU(String sql, String str[]) {
        int a = 0;
        Connection conn = null;
		PreparedStatement ps = null;
        try {
        	Class.forName(driverName);
			conn = DriverManager.getConnection(url);
            ps = conn.prepareStatement(sql);
            if (str != null) {
                for (int i = 0; i < str.length; i++) {
                    ps.setString(i + 1, str[i]);
                }
            }
            a = ps.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return a;
    }
	
	
		

}
	
